<?php

require_once ROOT_PATH . '/lib/confs/Conf.php';
require_once ROOT_PATH . '/lib/dao/DMLFunctions.php';
require_once ROOT_PATH . '/lib/dao/SQLQBuilder.php';
require_once ROOT_PATH . '/lib/common/CommonFunctions.php';
require_once ROOT_PATH . '/lib/common/UniqueIDGenerator.php';
require_once ROOT_PATH . '/lib/common/SearchObject.php';
require_once ROOT_PATH . '/lib/models/hrfunct/EmpInfo.php';



class SkillIndividual {

	const TABLE_NAME = 'hs_hr_skill_individual';
	
	const DB_FIELD_SKILL_INDIVIDUAL_ID = 'ID';
	const DB_FIELD_SKILL_INDIVIDUAL_USER_ID = 'UserID';	
	const DB_FIELD_SKILL_INDIVIDUAL_NUM_OF_YEAR = 'NumOfYear';
	const DB_FIELD_SKILL_INDIVIDUAL_WORK_ABROAD = 'WorkAbroad';
	const DB_FIELD_SKILL_INDIVIDUAL_EXPERTISE = 'Expertise';
	const DB_FIELD_SKILL_INDIVIDUAL_DATE_SUBMIT = 'DateSubmit';
	
	private $skill_individual_id;
	private $skill_individual_user_id;
	private $skill_individual_num_of_year;
	private $skill_individual_work_abroad;
	private $skill_individual_expertise;
	private $skill_individual_date_submit;
	
	
	public function __construct($id = null) {
		$this->skill_individual_id = $id;
	}

	public function setId($id) {
		$this->skill_individual_id = $id;
	}
	
	public function getId() {
		return $this->skill_individual_id;
	}
	
	public function setSkillIndividualUserID($userID) {
		$this->skill_individual_user_id = $userID;
	}
	
	public function getSkillIndividualUserID() {
		return $this->skill_individual_user_id;
	}
	
	public function setSkillIndividualNumOfYear($num){
		$this->skill_individual_num_of_year = $num;
	}
	
	public function getSkillIndividualNumOfYear(){
		return $this->skill_individual_num_of_year;
	}
	
	public function setSkillIndividualWorkAbroad($workAbroad){
		$this->skill_individual_work_abroad = $workAbroad;
	}
	
	public function getSkillIndividualWorkAbroad(){
		return $this->skill_individual_work_abroad;
	}
	
	public function setSkillIndividualExpertise($expertise){
		$this->skill_individual_expertise =$expertise; 
	}
	
	public function getSkillIndividualExpertise(){
		return $this->skill_individual_expertise;
	}
	
	public function setSkillIndividualDateSubmit($date){
		$this->skill_individual_date_submit = $date;
	}
	
	public function getSkillIndividualDateSubmit(){
		return $this->skill_individual_date_submit;
	}

    public function save() {
		if (isset($this->skill_individual_id)) {
			return $this->_update();
		} else {
			return $this->_insert();
		}
    }

	public static function delete($ids) {

		$count = 0;
		if (!is_array($ids)) {
			throw new SkillIndividualException("Invalid parameter to delete(): ids should be an array", SkillIndividual::INVALID_PARAMETER);
		}		
		return $count;
	}	

	public static function getAll() {
		return self::_getList();
	}
	
	public function getSkillIndividualByUser($userID){
		$selectCondition[0] = "(". self::DB_FIELD_SKILL_INDIVIDUAL_USER_ID ." = ". $userID." )";
		$skillIndividuals = self::_getList($selectCondition);
		if(count($skillIndividuals)>0)
			return $skillIndividuals[0];
		else
			return new SkillIndividual();
	}
	
	private static function _getList($selectCondition = null,$selectOrderBy=null,$selectOrder=null, $selectLimit=null) {
		$fields[0] = self::DB_FIELD_SKILL_INDIVIDUAL_ID;
		$fields[1] = self::DB_FIELD_SKILL_INDIVIDUAL_USER_ID;
		$fields[2] = self::DB_FIELD_SKILL_INDIVIDUAL_NUM_OF_YEAR;
		$fields[3] = self::DB_FIELD_SKILL_INDIVIDUAL_WORK_ABROAD;
		$fields[4] = self::DB_FIELD_SKILL_INDIVIDUAL_EXPERTISE;
		$fields[5] = self::DB_FIELD_SKILL_INDIVIDUAL_DATE_SUBMIT;						
		
		$tables[0] = self::TABLE_NAME ;
		
		$sqlBuilder = new SQLQBuilder();
		$sql = $sqlBuilder->simpleSelect($tables[0],$fields,$selectCondition,$selectOrderBy,$selectOrder, $selectLimit);
		//die($sql);
		$actList = array();

		$conn = new DMLFunctions();
		$result = $conn->executeQuery($sql);

		while ($result && ($row = mysql_fetch_assoc($result))) {
			$actList[] = self::_createFromRow($row);
		}
		return $actList;
	}

	private function _insert() {
				
		//$fields[0] = self::DB_FIELD_SURVEY_ID;
		$fields[0] = self::DB_FIELD_SKILL_INDIVIDUAL_USER_ID;
		$fields[1] = self::DB_FIELD_SKILL_INDIVIDUAL_NUM_OF_YEAR;
		$fields[2] = self::DB_FIELD_SKILL_INDIVIDUAL_WORK_ABROAD;
		$fields[3] = self::DB_FIELD_SKILL_INDIVIDUAL_EXPERTISE;
		$fields[4] = self::DB_FIELD_SKILL_INDIVIDUAL_DATE_SUBMIT;
		

		//$values[0] = $this->survey_id;
		$values[0] = $this->skill_individual_user_id;
		$values[1] = $this->skill_individual_num_of_year;		
		$values[2] = $this->skill_individual_work_abroad;
		$values[3] = $this->skill_individual_expertise;
		$values[4] = $this->skill_individual_date_submit?$this->skill_individual_date_submit:'0000-00-00';				
		
		$sqlBuilder = new SQLQBuilder();
		$sqlBuilder->table_name = self::TABLE_NAME;
		$sqlBuilder->flg_insert = 'true';
		$sqlBuilder->arr_insert = $values;
		$sqlBuilder->arr_insertfield = $fields;

		$sql = $sqlBuilder->addNewRecordFeature2();

		$conn = new DMLFunctions();

		$result = $conn->executeQuery($sql);
		if (!$result || (mysql_affected_rows() != 1)) {
			throw new SkillUserException("Insert failed. ", SkillUserException::DB_ERROR);
		}

		return $this->skill_individual_id;
	}

	private function _update() {

		$fields[0] = self::DB_FIELD_SKILL_INDIVIDUAL_ID;
		$fields[1] = self::DB_FIELD_SKILL_INDIVIDUAL_USER_ID;
		$fields[2] = self::DB_FIELD_SKILL_INDIVIDUAL_NUM_OF_YEAR;
		$fields[3] = self::DB_FIELD_SKILL_INDIVIDUAL_WORK_ABROAD;
		$fields[4] = self::DB_FIELD_SKILL_INDIVIDUAL_EXPERTISE;
		$fields[5] = self::DB_FIELD_SKILL_INDIVIDUAL_DATE_SUBMIT;
		
	
		$values[0] = $this->skill_individual_id;
		$values[1] = $this->skill_individual_user_id;
		$values[2] = $this->skill_individual_num_of_year;		
		$values[3] = $this->skill_individual_work_abroad;
		$values[4] = $this->skill_individual_expertise;		
		$values[5] = $this->skill_individual_date_submit?$this->skill_individual_date_submit:'0000-00-00';
				
		$sqlBuilder = new SQLQBuilder();
		$sqlBuilder->table_name = self::TABLE_NAME;
		$sqlBuilder->flg_update = 'true';
		$sqlBuilder->arr_update = $fields;
		$sqlBuilder->arr_updateRecList = $values;

		$sql = $sqlBuilder->addUpdateRecord1(0);

		$conn = new DMLFunctions();
		$result = $conn->executeQuery($sql);

		if (!$result) {
			throw new SkillUserException("Update failed. SQL=$sql", SkillUserException::DB_ERROR);
		}
		return $this->skill_individual_id;
	}

    private static function _createFromRow($row) {
    	
    	$skillIndividual = new SkillIndividual();
    	
    	$skillIndividual->setId($row[self::DB_FIELD_SKILL_INDIVIDUAL_ID]);
    	$skillIndividual->setSkillIndividualUserID($row[self::DB_FIELD_SKILL_INDIVIDUAL_USER_ID]);
    	$skillIndividual->setSkillIndividualNumOfYear($row[self::DB_FIELD_SKILL_INDIVIDUAL_NUM_OF_YEAR]);
    	$skillIndividual->setSkillIndividualWorkAbroad($row[self::DB_FIELD_SKILL_INDIVIDUAL_WORK_ABROAD]);
    	$skillIndividual->setSkillIndividualExpertise($row[self::DB_FIELD_SKILL_INDIVIDUAL_EXPERTISE]);
    	$skillIndividual->setSkillIndividualDateSubmit($row[self::DB_FIELD_SKILL_INDIVIDUAL_DATE_SUBMIT]);
    	
    	return $skillIndividual;
    }
    
 	function getEmpSkillInfo($expertise = null) {

        $tables[0] = 'hs_hr_employee e';        
        $tables[1] = '`hs_hr_job_title` b';
        $tables[2] = "`hs_hr_compstructtree` d";
        $tables[3] = 'hs_hr_empstat h';
        $tables[4] = 'hs_hr_skill_individual s';
       

        $joinConditions[1] = 'e.`job_title_code` = b.`jobtit_code`';
        $joinConditions[2] = "e.`work_station` = d.`id`";
        $joinConditions[3] = "e.`emp_status` = h.`estat_code`";
        $joinConditions[4] = "e.`emp_number` = s.`UserID`";          
        
      
        $arrFieldList[0] = 'e.emp_lastname';
        $arrFieldList[1] = 'e.emp_firstname';
        $arrFieldList[2] = 'e.emp_middle_name';
        $arrFieldList[3] = 'e.emp_number';

        $sql_builder = new SQLQBuilder();
        $selectConditions[0] = " `emp_status` != 'EST000'";
        if($expertise)
        {
        	$selectConditions[1] = " `Expertise` IN ($expertise)";	
        }

        $sqlBuilder = new SQLQBuilder();
        $sqlQString = $sqlBuilder->selectFromMultipleTable($arrFieldList,$tables, $joinConditions, $selectConditions);

        $dbConnection = new DMLFunctions();
        $message2 = $dbConnection->executeQuery($sqlQString); //Calling the addData() function

        $i = 0;
		$arrayDispList = array();
        while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {

            $arrayDispList[$i][0] = $line[0];
            $arrayDispList[$i][1] = $line[1];
            $arrayDispList[$i][2] = $line[2];
             $arrayDispList[$i][3] = $line[3];
           
            $i++;

        }

        if (isset ($arrayDispList)) {

            return $arrayDispList;

        } else {

            return $arrayDispList;

        }
    }
    
      function getEmpSkillUsers($user =null) {

        $tables[0] = 'hs_hr_employee e';        
        $tables[1] = '`hs_hr_skill_user` b';

        $joinConditions[1] = "e.`emp_number` = b.`UserID`";          

        $arrFieldList[0] = 'e.emp_number';
        
        $arrFieldList[1] = 'b.expertiseID';
        $arrFieldList[2] = 'b.value';
        $arrFieldList[3] = 'b.DateSubmit';
        

        $sql_builder = new SQLQBuilder();
        $selectConditions[0] = " `EMP_STATUS` != 'EST000'";
        if($user){
        	$selectConditions[1] = " `emp_number` = $user";
        }
        //$joinTypes = ""

        $sqlBuilder = new SQLQBuilder();
        $sqlQString = $sqlBuilder->selectFromMultipleTable($arrFieldList,$tables, $joinConditions, $selectConditions, null, "e.emp_number",null,null,"b.id");
        //die($sqlQString);
		$dbConnection = new DMLFunctions();
        $message2 = $dbConnection->executeQuery($sqlQString); //Calling the addData() function

        $i = 0;
		$arrayDispList = array();
        while ($line = mysql_fetch_array($message2, MYSQL_NUM)) {

            $arrayDispList[$i][0] = $line[0];
            $arrayDispList[$i][1] = $line[1];
            $arrayDispList[$i][2] = $line[2];
            $arrayDispList[$i][3] = $line[3];             
            $i++;

        }

        if (isset ($arrayDispList)) {

            return $arrayDispList;

        } else {

            return $arrayDispList;

        }
    }
}

class SkillIndividualException extends Exception {
	const INVALID_PARAMETER = 0;
	const DB_ERROR = 1;
}
?>

